import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DatabaseHelper {
  final latestVersion = 2;
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('database.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);
    var db = await openDatabase(path,
        version: latestVersion, onCreate: _createDB, onUpgrade: _updateDB);
    return db;
  }

  Future<void> _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE memo (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        content TEXT,
        tags TEXT,
        created_time INTEGER,
        modified_time INTEGER
      )
    ''');
  }

  Future<void> _updateDB(Database db, int oldVersion, int newVersion) async {
    try {
      await db.execute('ALTER TABLE memo ADD COLUMN tags TEXT');
      await db.update('memo', {'tags': null});
    } on DatabaseException catch (e) {
      if (e.isDuplicateColumnError()) {
        // 忽略该异常，该列已经存在
      } else {
        rethrow;
      }
    }
    await db.setVersion(newVersion);
  }
}
